#!/usr/bin/python
# -*- coding:utf-8 -*-
import re
import sqlite3
import sys
from PySide2 import QtWidgets
from PySide2.QtGui import QStandardItemModel, QStandardItem
from PySide2.QtMultimedia import QSound
from PySide2.QtWidgets import QFileDialog, QMessageBox, QTableWidgetItem, QComboBox, QDialog, QVBoxLayout, QLabel, \
    QLineEdit, QPushButton, QHBoxLayout
from importDataSettingWindow_ui import Ui_databaseReadWindow

class importDBMainWindow(QtWidgets.QMainWindow, Ui_databaseReadWindow):
    def __init__(self, conn, table_name, main_well_name, main_window):
        super().__init__()
        self.main_window = main_window  # 保存主窗口的引用
        self.model = None
        self.setupUi(self)
        self.conn = conn  # 接收的数据库连接
        self.table_name = table_name  # 接收的数据库表名
        self.main_well_name = main_well_name  # 主井名
        self.field_matching = []

        # 将按钮的点击事件连接到自定义函数
        self.selectFilePushButton.clicked.connect(self.open_file_dialog)
        self.cancelPushButton.clicked.connect(self.cancelWindow)
        self.savePushButton.clicked.connect(self.field_match)
        self.refreshPushButton.clicked.connect(self.refresh_table)
        self.addPushButton.clicked.connect(self.show_add_field_dialog)
        self.delPushButton.clicked.connect(self.handle_field_deletion)
        self.reloadPushButton.clicked.connect(self.reload_filed)
        # 预加载函数
        # self.get_tables_from_sql(self.conn)
        self.database_table_field()

    def read_las(self, file_name):
        try:
            headers = []
            self.data = []
            section = None
            with open(file_name, 'r') as file:
                for line in file:
                    line = line.strip()
                    if line.startswith('~'):
                        section = line
                    elif section == "~CURVE INFORMATION" and line and not line.startswith('#'):
                        # Extract headers from the curve information block
                        parts = line.split()
                        if parts:
                            headers.append(parts[0])  # Append the first part which is the mnemonic
                self.field_matching = headers  # 将获取到的表头存储
                self.setModel(headers)
                self.update_comboboxes(headers)  # 更新下拉框内容

        except Exception as e:
            print(f"Error reading LAS file: {e}")

    # 获取txt格式的表头名
    def get_header_for_txt(self, file_name):
        # 读取文件内容
        encodings = ['utf-8', 'gbk', 'latin1']
        for enc in encodings:
            try:
                with open(file_name, 'r', encoding=enc) as file:
                    first_line = file.readline().strip()
                    headers = first_line.split()
                    print(f"表头信息: {headers}")

                    self.field_matching = headers  # 将获取到的表头存储
                    self.setModel(headers)
                    self.update_comboboxes(headers)  # 更新下拉框内容
                    break
            except UnicodeDecodeError:
                print(f"无法使用编码 {enc} 读取文件，尝试其他编码...")
            except Exception as e:
                print(f"读取文件时发生错误: {e}")

    # 弹出选择文件弹窗
    def open_file_dialog(self):
        if self.sender() == self.selectFilePushButton:
            # 根据选择的文件类型设置文件过滤器
            if self.fileType.isChecked():
                file_filter = "文本文件 (*.txt)"
            elif self.fileType2.isChecked():
                file_filter = "LAS文件 (*.las)"
            else:
                # 如果没有选择任何文件类型，弹出提示框
                QMessageBox.warning(self, "提示", "请选择读取文件类型")
                return

            # 使用提供的样式打开文件对话框
            file_name, _ = QFileDialog.getOpenFileName(self, "选择文件", "", file_filter)
            self.file_path = file_name
            if file_name:
                # 使用正则表达式匹配文件名
                file_name_only = re.search(r'[^/]*$', file_name).group(0) if file_name else ""
                self.fileName.setText(file_name_only)
                print(f"你选择的文件是: {file_name}")

                if self.fileType.isChecked():
                    self.get_header_for_txt(file_name)
                if self.fileType2.isChecked():
                    self.read_las(file_name)

    # 将刷新文件字段名 tableView绑定
    def setModel(self, headers):
        # 创建QStandardItemModel并设置表头
        model = QStandardItemModel()
        model.setHorizontalHeaderLabels(['表头名', '列数'])

        # 将表头信息和列数添加到模型中
        for i, header in enumerate(headers):
            item_header = QStandardItem(header)
            item_index = QStandardItem(str(i + 1))
            model.appendRow([item_header, item_index])

        # 将模型设置为tableView的数据模型
        self.tableView.setModel(model)

    '''
    刷新按钮
    '''
    def refresh_table(self):
        if self.fileType.isChecked():
            self.get_header_for_txt(self.file_path)
        if self.fileType2.isChecked():
            self.read_las(self.file_path)

    """
    重载字段
    """

    def reload_filed(self):
        self.database_table_field()
        QSound.play("complete.wav")
        QMessageBox.warning(self, "提示", "已重新载入字段")

        return



    """
    接收添加的字段,进行添加字段操作
    """

    def get_pre_add_Fileds(self, field_name, field_type):
        print(f"接收到的字段名: {field_name}, 数据类型: {field_type}")
        # 这里可以进行进一步的处理，例如更新UI，或者将这些信息添加到数据库等
        # 构建 SQL 语句以添加新字段，字段默认可为 NULL
        sql_add_field = f"ALTER TABLE {self.table_name} ADD COLUMN {field_name} {field_type} NULL;"

        cursor = self.conn.cursor()
        try:
            cursor.execute(sql_add_field)
            self.conn.commit()  # 确保提交更改
            print(f"字段 {field_name} 已成功添加到表 {self.table_name} 中")
            # 这里可以进行UI更新或其他逻辑处理
            self.database_table_field()
        except sqlite3.Error as e:
            print(f"添加字段时出错: {e}")
            QMessageBox.warning(self, "数据库错误", f"添加字段失败: {e}")

    '''
    删除数据库字段
    '''

    def handle_field_deletion(self):
        # 获取选中的字段名
        selected_indexes = self.tableView_2.selectionModel().selectedRows()
        if not selected_indexes:
            QMessageBox.warning(self, "选择错误", "请在表中选择一个字段")
            return

        selected_row = selected_indexes[0].row()  # 获取选中行的行号
        field_name_item = self.model.item(selected_row, 2)  # 假设字段名在第三列
        field_name = field_name_item.text() if field_name_item else None

        # 弹出一个询问对话框
        reply = QMessageBox.question(
            self,  # 通常是窗口的self引用
            '删除确认',  # 对话框标题
            f"你确定要删除字段 {field_name} 吗？",  # 显示的消息内容
            QMessageBox.Yes | QMessageBox.No,  # 包含的按钮
            QMessageBox.No  # 默认选中的按钮
        )

        # 根据用户的选择进行操作
        if reply == QMessageBox.No:
            return

        if not field_name:
            QMessageBox.warning(self, "错误", "无法获取字段名")
            return

        # 检查字段是否可以删除
        cursor = self.conn.cursor()
        check_query = f"SELECT EXISTS (SELECT 1 FROM {self.table_name} WHERE {field_name} IS NOT NULL LIMIT 1);"
        try:
            cursor.execute(check_query)
            exists = cursor.fetchone()[0]
            if exists != 0:
                QMessageBox.warning(self, "删除错误", "无法删除该字段，因为它包含非空数据")
                return
        except sqlite3.Error as e:
            QMessageBox.warning(self, "数据库错误", f"检查字段数据失败: {e}")
            return


        cursor1 = self.conn.cursor()
        query = f"PRAGMA table_info({self.table_name});"
        cursor1.execute(query)
        columns_info = cursor1.fetchall()
        columns = [col[1] for col in columns_info]  # 字段名在返回的元组的第二位置

        # 删除字段（由于SQLite限制，通过重建表实现）
        try:
            # 创建新表不包括要删除的字段
            columns.remove(field_name)
            fields = ', '.join(columns)
            cursor.execute(f"CREATE TABLE temp_table AS SELECT {fields} FROM {self.table_name};")
            cursor.execute(f"DROP TABLE {self.table_name};")
            cursor.execute(f"ALTER TABLE temp_table RENAME TO {self.table_name};")
            self.conn.commit()
            QMessageBox.information(self, "删除成功", f"字段 {field_name} 已成功删除。")
            self.database_table_field()
        except sqlite3.Error as e:
            QMessageBox.warning(self, "删除失败", f"删除字段失败: {e}")
            return



    '''
    数据库表字段
    '''
    def database_table_field(self):
        cursor = self.conn.cursor()
        query = f"PRAGMA table_info({self.table_name});"
        cursor.execute(query)
        results = cursor.fetchall()
        field_names = [field[1] for field in results]  # 获取字段名
        print(field_names)

        # 创建一个标准的Item模型
        self.model = QStandardItemModel(len(field_names), 5)
        self.model.setHorizontalHeaderLabels(['井名', '表名', '字段名', '表头名', '列数'])

        # 创建一个字典来存储对应关系
        self.field_to_header_mapping = {}

        # 填充表格数据
        for row, field_name in enumerate(field_names):
            self.model.setItem(row, 0, QStandardItem(self.main_well_name))
            self.model.setItem(row, 1, QStandardItem(self.table_name))
            self.model.setItem(row, 2, QStandardItem(field_name))

            # 第四列是下拉选择框，需要使用setIndexWidget来设置
            combo_box = QComboBox()
            combo_box.addItem("不添加")
            # 你可以在这里添加选项，例如：combo_box.addItems(["选项1", "选项2"])
            # combo_box.addItems(self.field_matching)
            self.tableView_2.setIndexWidget(self.model.index(row, 3), combo_box)
            combo_box.currentIndexChanged.connect(lambda index, r=row: self.update_column_index(r))

            # 第五列默认值为“无”
            self.model.setItem(row, 4, QStandardItem("无"))


        # 设置模型
        self.tableView_2.setModel(self.model)

    '''
    字段匹配
    '''

    def field_match(self):
        # 遍历模型中所有行
        for row in range(self.model.rowCount()):
            try:
                header_name = self.tableView_2.indexWidget(self.model.index(row, 3)).currentText()
            except AttributeError as e:
                QMessageBox.warning(self, "提示", "请先选择导入的文件!")
                return

            if header_name == "不添加":
                continue # 跳过当前迭代，继续下一个循环
            field_name = self.model.item(row, 2).text()

            column_number = self.model.item(row, 4).text()
            # 添加对应关系到字典中
            self.field_to_header_mapping[field_name] = (header_name, column_number)
        if self.fileType.isChecked():
            self.import_file_type = "txt"
        elif self.fileType2.isChecked():
            self.import_file_type = "las"
        else:
            QMessageBox.warning(self, "提示", "系统错误!")
        mapping_data = {self.table_name: (self.main_well_name, self.file_path, self.import_file_type, self.field_to_header_mapping)}
        if self.field_to_header_mapping == {}:
            QMessageBox.warning(self, "提示", "请匹配字段!")
            return
        # mapping_data["字段"] = self.field_to_header_mapping
        # mapping_data["井名"] = self.main_well_name
        # mapping_data["表名"] = self.table_name
        # mapping_data["文件来源"] = self.file_path

        # print(self.mapping_data)
        # 发送数据
        self.main_window.receive_mapping_data(mapping_data)
        self.close()

    def show_add_field_dialog(self):
        dialog = AddFieldDialog(self, main_window=self)
        dialog.exec_()  # 显示对话框，并等待用户操作




    '''
    更新下拉框表头信息
    '''

    def update_comboboxes(self, headers):
        for row in range(self.model.rowCount()):
            index = self.model.index(row, 3)
            combo_box = self.tableView_2.indexWidget(index)
            if combo_box is not None:
                combo_box.clear()
                combo_box.addItem("不添加")
                combo_box.addItems(headers)
                combo_box.setCurrentIndex(0)
            else:
                # 如果 combo_box 为 None，重新创建并设置
                combo_box = QComboBox()
                combo_box.addItem("不添加")
                combo_box.addItems(headers)
                self.tableView_2.setIndexWidget(index, combo_box)
            combo_box.currentIndexChanged.connect(lambda index, r=row: self.update_column_index(r))

    def update_column_index(self, row):
        index = self.model.index(row, 3)
        combo_box = self.tableView_2.indexWidget(index)
        if combo_box is not None:
            header_text = combo_box.currentText()
            if header_text == "不添加":
                self.model.setItem(row, 4, QStandardItem("无"))
            elif header_text in self.field_matching:
                column_index = self.field_matching.index(header_text) + 1
                self.model.setItem(row, 4, QStandardItem(str(column_index)))




    # 取消按钮
    def cancelWindow(self):
        self.close()





class AddFieldDialog(QDialog):
    def __init__(self, parent=None, main_window=None):
        super(AddFieldDialog, self).__init__(parent)
        self.main_window = main_window  # 保存对主窗口的引用
        self.setWindowTitle("添加字段")
        self.setup_ui()

    def setup_ui(self):
        main_layout = QVBoxLayout(self)  # 主布局为垂直布局

        # 字段名布局
        field_name_layout = QHBoxLayout()  # 创建水平布局
        self.field_name_label = QLabel("字段名:", self)
        self.field_name_edit = QLineEdit(self)
        field_name_layout.addWidget(self.field_name_label)
        field_name_layout.addWidget(self.field_name_edit)
        main_layout.addLayout(field_name_layout)  # 将水平布局添加到主布局中

        # 数据类型选择布局
        type_layout = QHBoxLayout()  # 创建另一个水平布局
        self.type_label = QLabel("选择数据类型:", self)
        self.type_combo = QComboBox(self)
        self.type_combo.addItems(["INTEGER", "TEXT", "REAL", "BLOB"])
        type_layout.addWidget(self.type_label)
        type_layout.addWidget(self.type_combo)
        main_layout.addLayout(type_layout)  # 将水平布局添加到主布局中

        # 确认和取消按钮布局
        buttons_layout = QHBoxLayout()  # 创建按钮的水平布局
        self.ok_button = QPushButton("添加", self)
        self.ok_button.clicked.connect(self.add_field)  # 修改这里
        self.cancel_button = QPushButton("取消", self)
        self.cancel_button.clicked.connect(self.reject)  # 点击取消后关闭对话框
        buttons_layout.addWidget(self.ok_button)
        buttons_layout.addWidget(self.cancel_button)
        main_layout.addLayout(buttons_layout)  # 将按钮布局添加到主布局中

    def add_field(self):
        field_name = self.field_name_edit.text()
        field_type = self.type_combo.currentText()
        if self.main_window:
            self.main_window.get_pre_add_Fileds(field_name, field_type)
        self.accept()